package org.neuedu.ssmp.crm.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.*;
import org.neuedu.ssmp.crm.entity.ServiceManagement;
import org.neuedu.ssmp.crm.entity.ServeList;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.neuedu.ssmp.crm.entity.ServiceManagementHandle;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author MoonHolder
 * @since 2019-12-09
 */
public interface ServeListMapper extends BaseMapper<ServeList> {
    /**
     * 服务归档显示联合查询
     *
     * @return
     */
    @Select("select c.*,s.*,st.* from customer c join serve_list s on c.id=s.customer_id join serve_state st on s.state=st.id")
    List<ServeList> serviceArchiving();

    @Select(" SELECT se.`name` 'serviceType',sl.summary,cr.`name` clientName,ss.`name` stateName,sl.content requestService,user.username creatName,sl.create_time createTime,user2.username assignedName,sl.grant_time assignedTime,sl.operator_content operatorContent,user3.username handlerName,sl.operator_time handlerTime,sl.result_content processResult,sl.agree_level Satisfaction  FROM serve_state ss" +
            " JOIN serve_list sl ON ss.id=sl.state" +
            " JOIN sys_user user2 ON user2.id=sl.user_id" +
            " JOIN customer cr ON cr.id=sl.customer_id" +
            " JOIN sys_user user ON user.id=sl.create_user_id" +
            " JOIN sys_user user3 on user3.id=sl.operator_id" +
            " JOIN serve se ON sl.service_id=se.id" +
            " WHERE sl.agree_level>=3")
    List<ServiceManagement> findAllsServiceArchiving();

    /**
     * 服务反馈显示联合查询
     *
     * @return
     */
    @Select(" SELECT sl.id,se.`name` 'serviceType',sl.summary,cr.`name` clientName,ss.`name` stateName,sl.content requestService,user.username creatName,sl.create_time createTime,user2.username assignedName,sl.grant_time assignedTime,sl.operator_content operatorContent,user3.username handlerName,sl.operator_time handlerTime,sl.result_content processResult,sl.agree_level Satisfaction  FROM serve_state ss" +
            " JOIN serve_list sl ON ss.id=sl.state" +
            " JOIN sys_user user2 ON user2.id=sl.user_id" +
            " JOIN customer cr ON cr.id=sl.customer_id" +
            " JOIN sys_user user ON user.id=sl.create_user_id" +
            " JOIN sys_user user3 on user3.id=sl.operator_id" +
            " JOIN serve se ON sl.service_id=se.id" +
            " WHERE sl.agree_level<3")
    List<ServiceManagement> findAllsSsrviceFeedback();

    /**
     * 服务分配显示联合查询
     **/
    @Results({
            @Result(property = "creatName", column = "create_user_id", one = @One(select ="org.neuedu.ssmp.crm.mapper.ServeListMapper.findUserName")),
            @Result(property = "assignedName", column = "user_id", one = @One(select ="org.neuedu.ssmp.crm.mapper.ServeListMapper.findUserName")),
            @Result(property = "handlerName", column = "operator_id", one = @One(select ="org.neuedu.ssmp.crm.mapper.ServeListMapper.findUserName"))
    })
    @Select("SELECT sl.id,se.`name` 'serviceType',sl.summary,cr.`name` clientName,ss.`name` stateName, " +
            "       sl.content requestService,sl.create_user_id,sl.create_time createTime, " +
            "       sl.user_id,sl.grant_time assignedTime,sl.operator_content operatorContent, " +
            "       sl.operator_id,sl.operator_time handlerTime,sl.result_content processResult, " +
            "       sl.agree_level Satisfaction FROM serve_state ss " +
            "       JOIN serve_list sl ON ss.id = sl.state " +
            "       JOIN customer cr ON cr.id = sl.customer_id " +
            "       JOIN serve se ON sl.service_id = se.id ${ew.customSqlSegment} ")
    List<ServiceManagementHandle> conditionQuery(@Param(Constants.WRAPPER) Wrapper<ServiceManagementHandle> wrapper);

    @Select("select username from sys_user where id = #{userId}")
    String findUserName(int userId);

    /**
     * @return服务归档搜索
     */
    @Select({"<script>",
            " SELECT sl.id,se.`name` 'serviceType',sl.summary,cr.`name` clientName,ss.`name` stateName,sl.content requestService,user.username creatName,sl.create_time createTime,user2.username assignedName,sl.grant_time assignedTime,sl.operator_content operatorContent,user3.username handlerName,sl.operator_time handlerTime,sl.result_content processResult,sl.agree_level Satisfaction  FROM serve_state ss" +
                    " JOIN serve_list sl ON ss.id=sl.state" +
                    " JOIN sys_user user2 ON user2.id=sl.user_id" +
                    " JOIN customer cr ON cr.id=sl.customer_id" +
                    " JOIN sys_user user ON user.id=sl.create_user_id" +
                    " JOIN sys_user user3 on user3.id=sl.operator_id" +
                    " JOIN serve se ON sl.service_id=se.id" +
                    " WHERE sl.agree_level&gt;2",
            "<when test='inputClientName!=null'>",
            "AND cr.name like #{inputClientName} ",
            "</when>",
            "<when test='inputSummry!=null'>",
            "AND sl.summary like #{inputSummry} ",
            "</when>",
            "<when test='creatTimes!=null'>",
            "AND sl.create_time like #{creatTimes}  ",
            "</when>",
            "</script>"})
    List<ServiceManagement> serviceArchiveSearch(@Param("inputClientName") String inputClientName, @Param("inputSummry") String inputSummry, @Param("creatTimes") String creatTimes);

    /**
     * @return服务反馈搜索
     */
    @Select({"<script>",
            " SELECT sl.id,se.`name` 'serviceType',sl.summary,cr.`name` clientName,ss.`name` stateName,sl.content requestService,user.username creatName,sl.create_time createTime,user2.username assignedName,sl.grant_time assignedTime,sl.operator_content operatorContent,user3.username handlerName,sl.operator_time handlerTime,sl.result_content processResult,sl.agree_level Satisfaction  FROM serve_state ss" +
                    " JOIN serve_list sl ON ss.id=sl.state" +
                    " JOIN sys_user user2 ON user2.id=sl.user_id" +
                    " JOIN customer cr ON cr.id=sl.customer_id" +
                    " JOIN sys_user user ON user.id=sl.create_user_id" +
                    " JOIN sys_user user3 on user3.id=sl.operator_id" +
                    " JOIN serve se ON sl.service_id=se.id" +
                    " WHERE sl.agree_level&lt;3",
            "<when test='inputClientName!=null'>",
            "AND cr.name like #{inputClientName} ",
            "</when>",
            "<when test='inputSummry!=null'>",
            "AND sl.summary like #{inputSummry} ",
            "</when>",
            "<when test='creatTimes!=null'>",
            "AND sl.create_time like #{creatTimes}  ",
            "</when>",
            "</script>"})
    List<ServiceManagement> serviceFeedbackSearch(@Param("inputClientName") String inputClientName, @Param("inputSummry") String inputSummry, @Param("creatTimes") String creatTimes);
}
